DECLARE @ksrq ut_rq16,@jsrq ut_rq16,@czyh VARCHAR(10)

SET @ksrq='2024120100:00:00'
SET @jsrq='2025093023:59:59'
SET @czyh='Z0267'


select
    b.syxh,
       c.name czym,
--        sum(case when czlb in(2) then isnull(b.jje,$0)-isnull(dje,$0)  end) zfje,
--        sum(case when czlb in(8,9) then isnull(b.jje,$0)-isnull(dje,$0) end) kkje
-- case when czlb in(2) then isnull(b.jje,$0)-isnull(dje,$0)  end zfje,
--        case when czlb in(8,9) then isnull(b.jje,$0)-isnull(dje,$0) end kkje
sum(case when czlb in(2) then isnull(b.jje,$0)-isnull(dje,$0)  end) zfje,
       sum(case when czlb in(8,9) then isnull(b.jje,$0)-isnull(dje,$0) end) kkje
-- into #zfje
from ZYB_SFJZK a(nolock)
inner join ZYB_BRYJK b(Nolock) on a.czyh=b.czyh and b.lrrq between a.ksrq and a.jzrq and b.czlb in(0,1,2,3,4,5,6,8,9)
left join YY_ZGBMK c(nolock) on a.czyh=c.id
where a.jzrq between @ksrq and @jsrq AND a.czyh=@czyh --and syxh='1322'
group by c.name
       ,b.syxh
having sum(case when czlb in(2) then isnull(b.jje,$0)-isnull(dje,$0)  end)<>sum(case when czlb in(8,9) then isnull(b.jje,$0)-isnull(dje,$0) end)


select b.czym 收费员
	,sum(case when lx='07' then c.je else $0 end) 统筹基金
	,sum(case when lx='09' then c.je else $0 end) 公补基金
	,sum(case when lx='10' then c.je else $0 end) 企业补充
	,sum(case when lx='11' then c.je else $0 end) 居民大病
	,sum(case when lx='12' then c.je else $0 end) 职工大额
	,sum(case when lx='13' then c.je else $0 end) 医疗救助
	,sum(case when lx='14' then c.je else $0 end) 其他支出
	,sum(case when lx='17' then c.je else $0 end) 个人账户
	,sum(case when lx='21' then c.je else $0 end) 账户共济
     ,sum(case when lx='18' then c.je else $0 end) 个人现金
	,$0 应缴款,$0 结算扣卡金额
into #temp1 from ZY_BRJSK a(nolock)
inner join ZYB_SFJZK b(nolock) on a.jsczyh=b.czyh and a.jsrq between b.ksrq and b.jzrq
inner join VW_ZYBRJSJEK c(nolock) on a.xh=c.jsxh
where a.ybjszt=2 and b.jzrq between @ksrq and @jsrq AND jsczyh=@czyh
and a.xh in(select jsxh from VW_ZYBRJSJEK b(nolock) where lx='01')
group by b.czym

insert into #temp1
select czym,$0,$0,$0,$0,$0,$0,$0,$0,$0,$0,zfje,kkje
from #zfje


select 收费员,@ksrq+'--'+@jsrq 查询时间,'全班结账'类型,sum(统筹基金)统筹基金,sum(公补基金)公补基金,sum(企业补充)企业补充,sum(居民大病)居民大病
						    ,sum(职工大额)职工大额,sum(医疗救助)医疗救助,sum(其他支出)其他支出,sum(个人账户)个人账户,sum(账户共济)账户共济,sum(个人现金) 个人现金
							,sum(结算扣卡金额)结算扣卡金额,sum(统筹基金+公补基金+企业补充+居民大病+职工大额+医疗救助+其他支出+个人账户
							+结算扣卡金额) 合计,sum(应缴款)应缴款
from #temp1 group by 收费员
union all
select '合计',@ksrq+'--'+@jsrq 查询时间,''  ,sum(统筹基金)统筹基金,sum(公补基金)公补基金,sum(企业补充)企业补充,sum(居民大病)居民大病
						    ,sum(职工大额)职工大额,sum(医疗救助)医疗救助,sum(其他支出)其他支出,sum(个人账户)个人账户,sum(账户共济)账户共济,sum(个人现金)
							,sum(结算扣卡金额),sum(统筹基金+公补基金+企业补充+居民大病+职工大额+医疗救助+其他支出+个人账户
							+结算扣卡金额) ,sum(应缴款)应缴款
from #temp1

DROP TABLE #zfje
DROP TABLE #temp1



-- select * from ZYB_BRYJK where syxh='828' and czlb in (2)
-- select * from ZYB_BRYJK where syxh='828' and czlb in (8,9)
-- select 116.7600-8.6800
-- select * from ZYB_BRYJK where syxh='828'
-- select * from ZY_BRJSK where syxh='828'
-- select * from VW_ZYBRJSJEK where jsxh='11575'


select top 100 * from ZY_NBRFYMXK_QXJS



